package com.wiserp.rest.helper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.wiserp.rest.model.OpResult;
import com.wiserp.rest.model.Transfer;
import com.wiserp.rest.model.TransferItem;

public class TransferHelper extends JdbcDaoSupport implements INounHelper {
	private static final String SQL_GET_UNPOSTED_TRANSFER = "select hbi001,hbi005,hbi008,hbi009 from shb08 WHERE hbi003='4n' AND hbi006='4' AND hbi100='a' ";
	private static final String SQL_GET_UNPOSTED_COUNT_TRANSFER = "SELECT COUNT(*) FROM shb08 WHERE hbi003='4n' AND hbi006='4' AND hbi100='a' ";
	private static final String SQL_GET_TRANSFERITEM_BY_FLOWNO = "SELECT hbj001,hbj004,hbj017,hbj021,hbj003,hbj011 FROM shb09 WHERE hbj002=?";
	private static final String SQL_GET_TRANSFER_BY_FLOWNO = "select hbi001,hbi005,hbi008,hbi009 from shb08 WHERE hbi001=? AND  hbi003='4n' AND hbi006='4' AND hbi100='a' ";
	private static final String SQL_UPDATE_TRANSFERITEM = "UPDATE shb09 SET hbj017=?,hbj021=?,hbj003=?,hbj011=? WHERE hbj001=? AND hbj002=?";

	private PlatformTransactionManager txManager;
	
	
	public List findUnpost() {
		return this.getJdbcTemplate().query(SQL_GET_UNPOSTED_TRANSFER,
				new RowMapper<Transfer>() {
					public Transfer mapRow(ResultSet rs, int i)
							throws SQLException {
						Transfer data = new Transfer();
						data.setFlowNo(rs.getString("hbi001"));
						data.setSrcWhNo(rs.getString("hbi008"));
						data.setDestWhNo(rs.getString("hbi009"));
						data.setBillDate(rs.getDate("hbi005"));
						return data;
					}
				});
	}

	
	
	public void setTxManager(PlatformTransactionManager txManager) {
		this.txManager = txManager;
	}



	public int getUnpostCount() {
		return this.getJdbcTemplate().queryForInt(
				SQL_GET_UNPOSTED_COUNT_TRANSFER);
	}
	
	public Object getByFlowNo(String flowNo) {
		Object result = null;
		try{
			result = this.getJdbcTemplate().queryForObject(SQL_GET_TRANSFER_BY_FLOWNO,
					new RowMapper<Transfer>() {
						public Transfer mapRow(ResultSet rs, int i)
								throws SQLException {
							Transfer data = new Transfer();
							data.setFlowNo(rs.getString("hbi001"));
							data.setSrcWhNo(rs.getString("hbi008"));
							data.setDestWhNo(rs.getString("hbi009"));
							data.setBillDate(rs.getDate("hbi005"));
							data.setItems(findItems(data.getFlowNo()));
							return data;
						}
					}, flowNo);
		}catch(EmptyResultDataAccessException e){ 
			result = OpResult.FAIL(String.format("单据(%s)不存在或者已过账或者已经上传过！", flowNo), 1);
		}
		return result;
	}

	public List findItems(String flowNo) {
		return this.getJdbcTemplate().query(SQL_GET_TRANSFERITEM_BY_FLOWNO, new RowMapper<TransferItem>() {
			public TransferItem mapRow(ResultSet rs, int i) throws SQLException {
				TransferItem data = new TransferItem();
				data.setItemNo(rs.getInt("hbj001"));
				data.setProdNo(rs.getString("hbj004"));
				data.setQty(rs.getDouble("hbj017"));
				data.setBatchNo(rs.getString("hbj021"));
				data.setSrcWhLocNo(rs.getString("hbj003"));
				data.setDestWhLocNo(rs.getString("hbj011"));
				return data;
			}
		}, flowNo);
	}
	
	public OpResult updateInfo(final Object bill){
		OpResult result = null;
		DefaultTransactionDefinition def = new DefaultTransactionDefinition();
		TransactionStatus status = txManager.getTransaction(def);
		try{
			result = updateInfoInternal(bill);
			txManager.commit(status);
		}catch(Exception ex){
			ex.printStackTrace();
			txManager.rollback(status);
			result = OpResult.UNKNOWNFAIL;
		}
		return result;
	}

	public OpResult updateInfoInternal(final Object bill) {
		Transfer data = (Transfer)bill;
		Object result = getByFlowNo(data.getFlowNo());
		if(result instanceof OpResult)
			return (OpResult)result;
		Transfer dataDB = (Transfer)result;
		StringBuffer msg = new StringBuffer("");
		for (TransferItem item : data.getItems()) {
			boolean found = false;
			for (TransferItem itemDB : dataDB.getItems()) {
				if (item.getItemNo() == itemDB.getItemNo()
						&& item.getProdNo().equals(itemDB.getProdNo())) {
					found = true;
					break;
				}
			}

			if (!found)
				msg.append(String.format(",项目编号(%d)物料编号为(%s)的项目在ERP中不存在",
						item.getItemNo(), item.getProdNo()));
		}

		if (msg.length() > 0) {
			msg.insert(0, String.format("上传的单据(%s)中", data.getFlowNo()))
					.append(",请检查");
			return OpResult.FAIL(msg.toString(), 2);
		}

		for (TransferItem itemDB : dataDB.getItems()) {
			boolean found = false;
			for (TransferItem item : data.getItems()) {
				if (item.getItemNo() == itemDB.getItemNo()
						&& item.getProdNo().equals(itemDB.getProdNo())) {
					found = true;
					break;
				}
			}

			if (!found)
				msg.append(String.format(",项目编号(%d)物料编号为(%s)的项目没有更新",
						itemDB.getItemNo(), itemDB.getProdNo()));
		}

		if (msg.length() > 0) {
			msg.insert(0, String.format("ERP的单据(%s)中", dataDB.getFlowNo()))
					.append(",请检查");
			return OpResult.FAIL(msg.toString(), 3);
		}
		
//		for(TransferItem itemDB:dataDB.getItems()){
//			double rcvQtyTotal = 0.0;
//			for(TransferItem item:data.getItems()){
//				if(item.getItemNo() == itemDB.getItemNo() && item.getProdNo().equals(itemDB.getProdNo()))
//					rcvQtyTotal += item.getRcvQty();
//			}
//			
//			if(rcvQtyTotal > itemDB.getQty())
//				msg.append(String.format(
//						",项目编号(%d)物料编号为(%s)的项目的接收数量大于可入库数量",
//						itemDB.getItemNo(), itemDB.getProdNo()));
//		}
//		
//
//		if (msg.length() > 0) {
//			msg.insert(0, String.format("上传的单据(%s)中", dataDB.getFlowNo()))
//					.append(",请检查");
//			return OpResult.FAIL(msg.toString(), 4);
//		}
		
		if(StringUtils.isNotEmpty(data.getOperatorNo()))
			this.getJdbcTemplate().update("UPDATE shb08 SET hbi017=?,hbi100='1' WHERE hbi001=?",data.getOperatorNo(),data.getFlowNo());
		else
			this.getJdbcTemplate().update("UPDATE shb08 SET hbi100='1' WHERE hbi001=?",data.getFlowNo());
		for(TransferItem itemDB:dataDB.getItems()){
			boolean updated = false;
			for(TransferItem item:data.getItems()){
				if(item.getItemNo() == itemDB.getItemNo() && item.getProdNo().equals(itemDB.getProdNo())){
					if(!updated){
						this.getJdbcTemplate().update(SQL_UPDATE_TRANSFERITEM, 
								item.getQty(),item.getBatchNo(),item.getSrcWhLocNo(),item.getDestWhLocNo(),item.getItemNo(),data.getFlowNo());
						updated = true;
					}else{
						copyInsert(data,item);
					}
				}
					
			}
		}
		
		return OpResult.SUCCESS;
	}

	
	private void copyInsert(Transfer data,TransferItem item){
		int maxNo = this.getJdbcTemplate().queryForInt("SELECT MAX(hbj001) FROM shb09 WHERE hbj002=?",data.getFlowNo());
		int nextNo = maxNo+10;
		this.getJdbcTemplate().update(
				"INSERT INTO shb09(hbj001,hbj002,hbj003,hbj004,hbj005,hbj006,hbj007,hbj008,hbj009,hbj010," +
				"hbj011,hbj012,hbj013,hbj014,hbj015,hbj016,hbj017,hbj018,hbj019,hbj020," +
				"hbj021,hbj022,hbj023,hbj024,hbj025,hbj026,hbj027,hbj028,hbj029,hbj030," +
				"hbj031,hbj032) " +
				"SELECT ?,hbj002,?,hbj004,hbj005,hbj006,hbj007,hbj008,hbj009,hbj010," +
				"?,hbj012,hbj013,hbj014,hbj014,hbj016,?,hbj018,hbj019,hbj020," +
				"?,hbj022,hbj023,hbj024,hbj025,hbj026,hbj027,hbj028,hbj029,hbj030," +
				"hbj031,hbj032 FROM shb09 WHERE hbj001=? AND hbj002=?",
				nextNo,item.getSrcWhLocNo(),item.getDestWhLocNo(),item.getQty(),item.getBatchNo(),item.getItemNo(),data.getFlowNo());
	}

}
